﻿
Partial Class WebForm_ManageFMExcelUpdate
    Inherits System.Web.UI.Page

#Region "事件"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Me.BindVersion()
            Me.BindDDLDivision()
            Me.BindDDLSignature()
        End If
    End Sub

    Protected Sub ddlDivision_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDivision.SelectedIndexChanged
        Me.BindDDLSignature()
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLS" AndAlso IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLSX" Then
            MyObj.ShowMessage("Import failed!!Please select the file extension (xls/xlsx) file!!")
            Return
        End If

        '檢查資料表是否存在
        Dim TableName As String = Me.ddlVersion.SelectedValue
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        sql.AppendFormat("Select * from sysobjects where name='{0}'", TableName)
        If Not dat.GetData(sql.ToString()).Rows.Count > 0 Then
            MyObj.ShowMessage("Table missed.")
            Return
        End If

        'Update by Angus 20140129
        '檢查此版本的Signature是否已經被Locked
        'sell in 有lock<> 不能上傳Material為N1,R1,B1的sku <> GV顯示錯誤
        'plv 有lock<> 不能上傳Material為plv sku(即非N1,R1,B1的sku) <> GV顯示錯誤
        Dim SFLock As Boolean = False
        Dim PLVLock As Boolean = False
        Dim vManager As New VersionManager(Me.ddlVersion.SelectedValue.Replace("SFReport", ""), Me.ddlSignature.SelectedValue, "")
        SFLock = vManager.Locked
        vManager = New VersionManager(Me.ddlVersion.SelectedValue.Replace("SFReport", ""), Me.ddlSignature.SelectedValue, "PLV_")
        PLVLock = vManager.Locked

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable

        '檢查工作表名稱
        Try
            If ExcelImportCheck.LoadData(dt, newName, "FuturMaster", IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
                Return
            End If
        Catch ex As Exception
            MyObj.WriteLog(ex.ToString())
            MyObj.ShowMessage("Please check the file format!!!")
            Exit Sub
        End Try

        '設定略過的列數
        Dim IgnareRows As Integer = 13
        '非月份欄位有4欄
        Dim BaseColumns As Integer = 4

        '設定欄位名稱
        For i As Integer = 0 To dt.Columns.Count - 1
            If dt.Rows(IgnareRows - 1)(i).ToString() <> "" Then
                dt.Columns(i).ColumnName = dt.Rows(IgnareRows - 1)(i).ToString().Replace("Sept", "Sep") 'Excel的9月是Sep
            End If
        Next

        '去掉不需要的月份
        For i As Integer = dt.Columns.Count - 1 To BaseColumns - 1 Step -1
            If IsDate(dt.Columns(i).ColumnName) Then
                If Convert.ToDateTime(dt.Columns(i).ColumnName).ToString("yyyyMM") < Me.YYMM Then
                    dt.Columns.RemoveAt(i)
                End If
            End If
        Next

        '存原始欄位名稱
        Dim ColNameList As New List(Of String)
        For i As Integer = 0 To dt.Columns.Count - 1
            ColNameList.Add(dt.Columns(i).ColumnName)
        Next

        '去掉空白列
        For i As Integer = IgnareRows - 1 To 0 Step -1
            If i <= dt.Rows.Count - 1 Then
                dt.Rows.RemoveAt(i)
            End If
        Next

        '設定開始年月
        Dim StartYear As Integer = Left(Me.YYMM, 4)
        Dim StartMonth As Integer = Right(Me.YYMM, 2)
        Dim TempDate As Date = New Date(StartYear, StartMonth, 1)
        '設定月份範圍
        Dim ScopeValue As Integer = dt.Columns.Count - BaseColumns

        Dim ErrorCount As Integer = 0
        Dim FilterValue As String = ""
        '存月份欄位字串
        Dim MonthString As String = ""
        Dim MyMonth As String = ""
        Dim MyDate As DateTime


        '如果月份欄位名稱填入非日期或是為F開頭的欄位,則直接刪掉該欄位
        For i As Integer = dt.Columns.Count - 1 To BaseColumns Step -1
            If Not IsDate(dt.Columns(i).ColumnName) Then
                If Left(dt.Columns(i).ColumnName.ToString(), 1) = "F" Then
                    dt.Columns.RemoveAt(i)
                Else
                    MyObj.ShowMessage("Column [{0}] is out of scope.", dt.Columns(i).ColumnName)
                    Return
                End If
            End If
        Next

        '上傳的月份數量必須在1~36個月之間
        If ScopeValue > 36 OrElse ScopeValue <= 0 Then
            MyObj.ShowMessage("The count of months is out of scope!!")
            Return
        Else
            For i As Integer = 0 To dt.Columns.Count - 1
                If Not IsDate(dt.Columns(i).ColumnName) Then
                    Select Case i
                        Case 0
                            dt.Columns(i).ColumnName = "ChannelID"
                        Case 1
                            dt.Columns(i).ColumnName = "Signature"
                        Case 2
                            dt.Columns(i).ColumnName = "SapCode"
                        Case 3
                            dt.Columns(i).ColumnName = "SkuType"
                    End Select
                Else
                    MyDate = Convert.ToDateTime(dt.Columns(i).ColumnName)
                    MyMonth = MyDate.Month
                    Dim YearGap As Integer = MyDate.Year - StartYear
                    dt.Columns(i).ColumnName = "Mon" & (Int32.Parse(MyMonth) + YearGap * 12)

                    If MonthString <> "" Then MonthString &= ","
                    MonthString &= "Mon" & (Int32.Parse(MyMonth) + YearGap * 12)
                End If

                If FilterValue <> "" Then FilterValue &= " or "
                FilterValue &= "convert(" & dt.Columns(i).ColumnName.ToString() & ",System.String)<>''"
            Next

            '新增Error欄位
            dt.Columns.Add("Error", System.Type.GetType("System.String"))

            Dim MyDefaultView As Data.DataView = dt.DefaultView
            MyDefaultView.RowFilter = FilterValue
            dt = MyDefaultView.ToTable()
        End If

        '暫存匯入的SapCode+SkuType
        Dim MatchDT As New Data.DataTable
        MatchDT.Columns.Add("SapCodeAndSkuType", System.Type.GetType("System.String"))
        MatchDT.PrimaryKey = New DataColumn() {MatchDT.Columns(0)}

        '取出所有SapCode資料
        Dim AllSapCodeDT As Data.DataTable
        sql.Length = 0
        sql.AppendFormat("Select distinct SapCode from {0} ", TableName)
        sql.AppendFormat("where Version1='M' and SkuType='S' and ChannelID='' ")
        AllSapCodeDT = dat.GetData(sql)
        AllSapCodeDT.PrimaryKey = New DataColumn() {AllSapCodeDT.Columns(0)}

        '取出有權限的SapCode資料
        Dim AuthSapCodeDT As Data.DataTable
        sql.Length = 0
        sql.AppendFormat("select distinct SapCode,CurrCode as NewCode,MaterialType,Active from ProductData p ")
        sql.AppendFormat("join DivisionBrandMappingView d on left(p.ProdHierarchy,2)=d.Brand ")
        If Me.ddlSignature.SelectedValue = "" Then
            sql.AppendFormat("and d.Division='{0}' ", Me.ddlDivision.SelectedValue)
        Else
            sql.AppendFormat("and d.Brand='{0}' ", Me.ddlSignature.SelectedValue)
        End If
        sql.AppendFormat("join RoleHierarchyMapping r on len(r.ProdHierarchy)=2 and d.Brand= r.ProdHierarchy ")
        sql.AppendFormat("join RoleUserMapping ru on r.RoleID =ru.RoleID ")
        sql.AppendFormat("where ru.userid='{0}' ", clsAuth.GetUserID())
        AuthSapCodeDT = dat.GetData(sql)
        AuthSapCodeDT.PrimaryKey = New DataColumn() {AuthSapCodeDT.Columns(0)}

        '取出有實際Sapcode+Channel存在的資料
        'Dim SapCodeChannelDT As New Data.DataTable
        'Dim cmanager As New ChannelManager
        'Dim stManager As New SignatureManager
        'sql.Length = 0
        'sql.AppendFormat("select distinct SapCode,cv.Channel from ProductData p ")
        'sql.AppendFormat("join ChannelView cv on left(p.ProdHierarchy,2)=cv.Signature ")
        'If Me.ddlSignature.SelectedValue <> "" Then
        '    sql.AppendFormat("and cv.Signature='{0}' ", Me.ddlSignature.SelectedValue)
        'Else
        '    sql.AppendFormat("and cv.Signature in ({0}) ", MyObj.JoinFormat(",", "'{0}'", stManager.GetSignatures(Me.ddlDivision.SelectedValue)))
        'End If
        'SapCodeChannelDT = dat.GetData(sql.ToString())
        'SapCodeChannelDT.PrimaryKey = New DataColumn() {SapCodeChannelDT.Columns("SapCode"), SapCodeChannelDT.Columns("Channel")}

        '開始檢查資料
        Dim NewRow As DataRow
        Dim MatchtString As String = ""
        Dim SapCode, Skutype, ChannelID As String
        For k As Integer = 0 To dt.Rows.Count - 1
            '去掉TW
            dt.Rows(k)("SapCode") = Replace(dt.Rows(k)("SapCode").ToString(), MyObj.GetAreaCode(), "")
            SapCode = dt.Rows(k)("SapCode").ToString().Trim()
            Skutype = dt.Rows(k)("Skutype").ToString().Trim()
            ChannelID = dt.Rows(k)("ChannelID").ToString().Trim()

            MatchtString = SapCode & Skutype & ChannelID
            If MatchDT.Rows.Contains(MatchtString) Then
                '比對SapCode加MaterialType加ChannelID是否重複
                dt.Rows(k)("Error") = "The SAP code is repetition."
            Else
                If SapCode <> "" Then
                    NewRow = MatchDT.NewRow()
                    NewRow("SapCodeAndSkuType") = MatchtString
                    MatchDT.Rows.Add(NewRow)
                End If

                Dim foundRow As DataRow = AuthSapCodeDT.Rows.Find(SapCode)
                'If Not AllSapCodeDT.Rows.Contains(SapCode) Then
                '比對SapCode是否存在目前的資料表裡
                'dt.Rows(k)("Error") = "The SAP code doesn't exist."
                'Else
                If Not AuthSapCodeDT.Rows.Contains(SapCode) Then
                    '比對SapCode是否在權限內
                    dt.Rows(k)("Error") = "The SAP code is out of your scope."
                ElseIf SFLock = True AndAlso (foundRow("MaterialType") = "N1" OrElse foundRow("MaterialType") = "R1" OrElse foundRow("MaterialType") = "B1") Then
                    'sell in 有lock→ 不能上傳Material為N1,R1,B1的sku <> GV顯示錯誤
                    dt.Rows(k)("Error") = "Version Locked."
                ElseIf PLVLock = True AndAlso (foundRow("MaterialType") <> "N1" AndAlso foundRow("MaterialType") <> "R1" AndAlso foundRow("MaterialType") <> "B1") Then
                    'plv 有lock<> 不能上傳Material為plv sku(即非N1,R1,B1的sku) <> GV顯示錯誤
                    dt.Rows(k)("Error") = "Version Locked."
                Else
                    'Active為Y或N,且不在該版本裡,須補基本資料
                    If (foundRow("Active") = "Y" OrElse foundRow("Active") = "N") AndAlso Not AllSapCodeDT.Rows.Contains(SapCode) Then
                        sql.Length = 0
                        sql.AppendFormat("exec BuildSFReportBySapCode '{0}','M','{1}'", TableName.Replace("SFReport", ""), SapCode)
                        'MyObj.WriteLog(sql.ToString())
                        dat.AccData(sql.ToString())
                    End If

                    'Active為T,須改SapCode為NewCode,且若NewCode不在該版本裡,則須補NewCode基本資料
                    If foundRow("Active") = "T" Then
                        dt.Rows(k)("SapCode") = foundRow("NewCode")
                        SapCode = dt.Rows(k)("SapCode")
                        If Not AllSapCodeDT.Rows.Contains(SapCode) Then
                            sql.Length = 0
                            sql.AppendFormat("exec BuildSFReportBySapCode '{0}','M','{1}'", TableName.Replace("SFReport", ""), SapCode)
                            'MyObj.WriteLog(sql.ToString())
                            dat.AccData(sql.ToString())
                        End If
                    End If

                    '每個月份檢查
                    For Each mon As String In MonthString.Split(",")
                        dt.Rows(k)(mon) = dt.Rows(k)(mon).ToString().Replace(" ", "")
                        If dt.Rows(k)(mon).ToString() <> "" Then
                            '欄位值不可為負,且不可為非數字　
                            If IsNumeric(dt.Rows(k)(mon).ToString()) = False OrElse Double.Parse(dt.Rows(k)(mon).ToString()) < 0 Then
                                'dt.Rows(k)("Error") = "Number should be positive."
                                'Exit For
                            End If

                            If IsNumeric(dt.Rows(k)(mon).ToString()) = False Then
                                dt.Rows(k)("Error") = "Column value should be Number."
                                Exit For
                            End If
                        Else
                            '空值存0,Add By Angus 2011/3/18
                            dt.Rows(k)(mon) = 0
                        End If

                        'SapCode SkyType 不可為空
                        If dt.Rows(k)(mon).ToString() <> "" And SapCode = "" Then
                            dt.Rows(k)("Error") = "The SAP code column shouldn't be empty."
                            Exit For
                        ElseIf dt.Rows(k)("SapCode").ToString().Length > 8 Then
                            dt.Rows(k)("Error") = "The SAP code length shouldn't over then 8."
                            Exit For
                        ElseIf dt.Rows(k)(mon).ToString() <> "" And Skutype = "" Then
                            dt.Rows(k)("Error") = "The type column couldn't be empty."
                            Exit For
                        End If

                        '比對SkuType範圍
                        If Skutype <> "" Then
                            Select Case Skutype
                                Case "BL", "Promo", "Launch"
                                Case Else
                                    dt.Rows(k)("Error") = "The type is out of Type Parameter."
                                    Exit For
                            End Select
                        End If
                    Next
                End If
            End If

            If dt.Rows(k)("Error").ToString() <> "" Then
                ErrorCount += 1
                Continue For
            End If
        Next

        '有異常則不寫入資料
        If ErrorCount > 0 Then
            MyObj.ShowMessage("Upload Failed.Data Error Count:" & ErrorCount)
        Else
            If dt.Rows.Count > 0 Then
                Dim MyRandom As New clsRandom
                Dim RandomCode As String = MyRandom.Generate(8)

                '新增欄位
                Dim Mydc As New DataColumn("RandomCode", System.Type.GetType("System.String"))
                Mydc.DefaultValue = RandomCode
                dt.Columns.Add(Mydc)

                Using copy As New SqlBulkCopy(ConfigurationManager.ConnectionStrings("connStr").ConnectionString, SqlBulkCopyOptions.KeepIdentity)
                    For i As Integer = 0 To dt.Columns.Count - 1
                        Select Case dt.Columns(i).ColumnName
                            Case "SapCode", "ChannelID", "SkuType", "RandomCode", "Error"
                                copy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName)
                            Case Else
                                If Left(dt.Columns(i).ColumnName, 3) = "Mon" Then
                                    copy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName)
                                End If
                        End Select
                    Next
                    copy.DestinationTableName = "SFReportU"
                    copy.WriteToServer(dt)
                End Using

                dt.Columns.Remove("RandomCode")

                '設定需要寫入的Signature  Add By Angus 20110825
                '=================================================
                Dim sigList As New List(Of String)
                If Me.ddlSignature.SelectedValue = "" Then
                    For i As Integer = 0 To Me.ddlSignature.Items.Count - 1
                        If Me.ddlSignature.Items(i).Value <> "" Then
                            sigList.Add(Me.ddlSignature.Items(i).Value)
                        End If
                    Next
                Else
                    sigList.Add(Me.ddlSignature.SelectedValue)
                End If
                '=================================================

                Dim NewRandomCode As String = ""
                For i As Integer = 0 To sigList.Count - 1
                    NewRandomCode = RandomCode & i.ToString()
                    sql.Length = 0
                    sql.AppendFormat("Update SFReportU ")
                    sql.AppendFormat("set RandomCode='{0}' ", NewRandomCode)
                    sql.AppendFormat("where RandomCode='{0}' ", RandomCode)
                    sql.AppendFormat("and SapCode in ")
                    sql.AppendFormat("(")
                    sql.AppendFormat("  Select SapCode from ProductData ")
                    sql.AppendFormat("  where Left(ProdHierarchy,2)='{0}' ", sigList(i))
                    sql.AppendFormat(");")

                    sql.AppendFormat("exec ImportFMQtyByUser @RandomCode='{0}' ", NewRandomCode)
                    sql.AppendFormat(",@SF_TableName='{0}' ", TableName)
                    sql.AppendFormat(",@Division='{0}' ", Me.ddlDivision.SelectedValue)
                    sql.AppendFormat(",@Signature='{0}' ", sigList(i))
                    sql.AppendFormat(",@EmployeeID='{0}' ", clsAuth.GetUserID())
                    'MyObj.WriteLog(sql.ToString())
                    dat.AccData(sql.ToString())
                Next

                MyObj.ShowMessage("Upload Successful.Data Success Count:" & dt.Rows.Count)
            Else
                MyObj.ShowMessage("Upload Failed.")
            End If

        End If

        '改顯示欄位名稱
        For i As Integer = 0 To ColNameList.Count - 1
            dt.Columns(i).ColumnName = ColNameList.Item(i).ToString()
        Next

        dt.DefaultView.RowFilter = "convert(Error,System.String)<>''"
        GVDT = dt.DefaultView.ToTable

        Me.BindGV()
    End Sub

    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub
#End Region

#Region "繫結資料"
    Protected Sub BindVersion()
        Me.ddlVersion.Items.Clear()
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy/MM"), "SFReport" & DateTime.Now.ToString("yyyyMM")))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.AddMonths(1).ToString("yyyy/MM"), "SFReport" & DateTime.Now.AddMonths(1).ToString("yyyyMM")))
        'Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy") & "Budget", "SFReport" & DateTime.Now.ToString("yyyy") & "B"))
        'Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy") & "Trend", "SFReport" & DateTime.Now.ToString("yyyy") & "T"))
        'Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.AddYears(1).ToString("yyyy") & "Budget", "SFReport" & DateTime.Now.AddYears(1).ToString("yyyy") & "B"))
        Me.ddlVersion.SelectedIndex = -1
    End Sub

    Private Sub BindDDLDivision()
        Dim ddlControl As DropDownList = Me.ddlDivision
        Dim dvManager As New DivisionManager

        ddlControl.DataTextField = "Division"
        ddlControl.DataValueField = "Division"
        ddlControl.DataSource = dvManager.GetAllDivision()
        ddlControl.DataBind()
    End Sub

    Private Sub BindDDLSignature()
        Dim ddlControl As DropDownList = Me.ddlSignature
        Dim stManager As New SignatureManager

        ddlControl.DataTextField = "Description"
        ddlControl.DataValueField = "Signature"
        ddlControl.DataSource = stManager.GetSignatureNoNewCategories(Me.ddlDivision.SelectedValue)
        ddlControl.DataBind()
        'ddlControl.Items.Insert(0, MyObj.GetDDLSelectAllItem())
    End Sub

    Protected Sub BindGV()
        gv.DataSource = GVDT
        gv.DataBind()
        If gv.Rows.Count > 0 Then
            gv.Visible = True
        Else
            gv.Visible = False
        End If
    End Sub
#End Region

#Region "屬性"
    Public Property GVDT() As Data.DataTable
        Get
            Return ViewState("GVDT")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("GVDT") = value
        End Set
    End Property

    Public ReadOnly Property YYMM() As String
        Get
            Return Me.ddlVersion.SelectedValue.Replace("SFReport", "")
        End Get
    End Property
#End Region
End Class
